<?php
/**
 * Created by PhpStorm.
 * User: apple
 * Date: 2017/8/1
 * Time: 下午10:10
 */

namespace excel;

class ExcelReader
{
    private $excel;

    public function __construct($filepath)
    {
        Vendor("PHPExcel");
        // TODO 可能读取出错
        $this->excel = \PHPExcel_IOFactory::load($filepath);
    }

    public function exec($titlesMap = [])
    {
        $data = $this->readData();
        !empty($titlesMap) && $data = $this->analysisData($data, $titlesMap);
        return $data;
    }

    private function analysisData($data, $titlesMap)
    {
        // 只提取有效字段
        $keys = [];
        foreach ($data[0] as $k => $title) {
            $index = array_search($title, $titlesMap);
            $index !== false && $keys[$k] = $index;
//            $keys[$k] = array_search($title, $titlesMap);
        }
//        p($keys, 0);
        $dataNew = [];
        $count = count($data);
        for ($i = 1; $i < $count; $i++) {
            $item = [];
            foreach ($data[$i] as $k => $value) {
                isset($keys[$k]) && $item[$keys[$k]] = $value;
            }
            $dataNew[] = $item;
        }
        return $dataNew;
    }

    private function readData()
    {
        $countRow = $this->getSheet()->getHighestRow();
        // 为英文字符 如Q 或 AB
        $countColumn = $this->getSheet()->getHighestColumn();

        $colChars = ['A'];
        while (true) {
            $charPrev = end($colChars);
            if ($charPrev == $countColumn) break;
            if (strlen($charPrev) == 1) {
                $colChars[] = $charPrev == 'Z' ? 'AA' :  chr(ord($charPrev) + 1);
            } else { // 2位的情况
//                list($charPrevFir, $charPrevSec) = explode($charPrev, '');
                $charPrevFir=substr($charPrev,0,1);
                $charPrevSec=substr($charPrev,1,1);
                $colChars[] = $charPrevSec == 'Z' ? chr(ord($charPrevFir) + 1) . 'A' : $charPrevFir . chr(ord($charPrevSec) + 1);
            }
        }
        $count = count($colChars);
        $data = [];
        for ($i = 1; $i <= $countRow; $i++) {
            $item = [];
            for ($j = 0; $j < $count; $j++) {
                $value = $this->getSheet()->getCell($colChars[$j] . $i)->getValue();
                is_null($value) && $value = '';
                $item[] = $value;
            }
            $data[] = $item;
        }
        return $data;
    }

    private function getSheet()
    {
        return $this->excel->getSheet(0);
    }
}